package com.lxu.report.service;

import com.lxu.report.entity.YwReportData;
import com.lxu.report.entity.bo.ReportDirBO;
import com.lxu.report.entity.bo.ReportExpDataBO;
import com.lxu.report.entity.enums.ReportEnum;
import org.apache.commons.collections4.CollectionUtils;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.RowMapper;
import org.springframework.stereotype.Service;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.List;

/**
 * 模板查詢服務
 */
@Service
public class TemplateQueryService {

    @Autowired
    private JdbcTemplate jdbcTemplate;

    /**
     * 查看某個項目下面的某個客戶对应的某个模板类型的报表模板目录
     *
     * @param reportData 模板报表
     * @return
     */
    public List<ReportDirBO> queryReportDirs(YwReportData reportData) {
        String strSql = "SELECT rr.ID,rr.DATA_SQL,rr.SORT,rr.DATA_TYPE,rr.TEMPLATE_FILE_PATH,rr.DEST_FILE_PATH,rd.PROJECT_ID,rd.CUST_ID,rr.MULTIPLE_TABLE_FLAG" +
                " FROM yw_report_data rd,yw_report_dir rr" +
                " WHERE rd.ID=rr.TEMPLATE_ID AND rd.PROJECT_ID=? AND rd.CUST_ID=?" +
                " AND rd.TEMPLATE_CODE=? AND rr.DIR_TYPE =? AND rr.DIR_STATUS=? ORDER BY rr.SORT";
        List<ReportDirBO> dirs = jdbcTemplate.query(strSql, new Object[]{reportData.getProjectId(),
                reportData.getCustomerId(), reportData.getTemplateCode(), ReportEnum.DIR_TYPE.EXPORT.getValue(),ReportEnum.DATA_STATUS.USED.getValue()}, new RowMapper<ReportDirBO>() {
            @Override
            public ReportDirBO mapRow(ResultSet rs, int rowNum) throws SQLException {
                YwReportData ywReportData = new YwReportData();
                ReportDirBO reportDirBO = new ReportDirBO();
                reportDirBO.setId(rs.getString(1));
                reportDirBO.setDataSql(rs.getString(2));
                reportDirBO.setSort(rs.getInt(3));
                reportDirBO.setDataType(rs.getString(4));
                reportDirBO.setTemplateFilePath(rs.getString(5));
                reportDirBO.setDestFilePath(rs.getString(6));
                ywReportData.setProjectId(rs.getString(7));
                ywReportData.setCustomerId(rs.getString(8));
                reportDirBO.setMultipleTableFlag(rs.getString(9));
                reportDirBO.setReportData(ywReportData);
                return reportDirBO;
            }
        });
        return dirs;
    }

    /**
     * 根据目录ID获取需要导出的配置
     *
     * @param id
     * @return
     */
    public List<ReportExpDataBO> queryDataByDir(String id) {
        String strSql = "SELECT t.DATA_SQL,t.START_POSITION,t.TABLE_INDEX,t.META_TYPE,t.MIN_ROWS " +
                " FROM yw_report_exp_data t WHERE t.DIR_ID=? AND t.EXP_STATUS=? ORDER BY t.EXP_SORT DESC";
        List<ReportExpDataBO> exps = jdbcTemplate.query(strSql, new Object[]{id,ReportEnum.DATA_STATUS.USED.getValue()}, new RowMapper<ReportExpDataBO>() {
            @Override
            public ReportExpDataBO mapRow(ResultSet rs, int rowNum) throws SQLException {
                ReportExpDataBO result = new ReportExpDataBO();
                result.setDataSql(rs.getString(1));
                result.setStartPosition(rs.getInt(2));
                result.setTableIndex(rs.getInt(3));
                result.setMetaType(rs.getString(4));
                result.setMinRows(rs.getInt(5));
                return result;
            }
        });
        return exps;
    }

    /**
     * 查询当前项目下用户的报告模板信息
     *
     * @param entity
     * @return
     */
    public YwReportData queryReportData(YwReportData entity) {
        String strSql = "SELECT t.ID,t.PROJECT_ID,t.CUST_ID,t.TEMPLATE_CODE,t.REPORT_ADDR" +
                " FROM yw_report_data t WHERE t.PROJECT_ID=? AND t.CUST_ID=?" +
                " AND t.TEMPLATE_CODE=? AND t.TEMPLATE_TYPE_CODE=?  ";
        List<YwReportData> reports = jdbcTemplate.query(strSql, new Object[]{entity.getProjectId(),
                entity.getCustomerId(), entity.getTemplateCode(),entity.getTemplateTypeCode()}, new RowMapper<YwReportData>() {
            @Override
            public YwReportData mapRow(ResultSet rs, int rowNum) throws SQLException {
                YwReportData result = new YwReportData();
                result.setId(rs.getString(1));
                result.setProjectId(rs.getString(2));
                result.setCustomerId(rs.getString(3));
                result.setTemplateCode(rs.getString(4));
                result.setReportAddr(rs.getString(5));
                return result;
            }
        });
        return CollectionUtils.isEmpty(reports) ? null : reports.get(0);
    }

    public YwReportData queryHTBFReportData(YwReportData entity) {
        String strSql = "SELECT t.ID,t.PROJECT_ID,t.CUST_ID,t.TEMPLATE_CODE,t.REPORT_ADDR" +
                " FROM yw_report_data t WHERE t.PROJECT_ID=? AND t.CONTRACT_TYPE=? " +
                " AND (t.TEMPLATE_CODE=? OR t.TEMPLATE_TYPE_CODE=?)  ";
        List<YwReportData> reports = jdbcTemplate.query(strSql, new Object[]{entity.getProjectId(),
                entity.getContractType(), entity.getTemplateCode(),entity.getTemplateTypeCode()}, new RowMapper<YwReportData>() {
            @Override
            public YwReportData mapRow(ResultSet rs, int rowNum) throws SQLException {
                YwReportData result = new YwReportData();
                result.setId(rs.getString(1));
                result.setProjectId(rs.getString(2));
                result.setCustomerId(rs.getString(3));
                result.setTemplateCode(rs.getString(4));
                result.setReportAddr(rs.getString(5));
                return result;
            }
        });
        return CollectionUtils.isEmpty(reports) ? null : reports.get(0);
    }

    public YwReportData querySingletonReportTemplate(YwReportData entity) {
        String strSql = "SELECT t.ID,t.PROJECT_ID,t.CUST_ID,t.TEMPLATE_CODE,t.REPORT_ADDR" +
                " FROM yw_report_data t WHERE t.PROJECT_ID=? " +
                " AND (t.TEMPLATE_CODE=? OR t.TEMPLATE_TYPE_CODE=?)  ";
        List<YwReportData> reports = jdbcTemplate.query(strSql, new Object[]{entity.getProjectId(),
                entity.getTemplateCode(),entity.getTemplateTypeCode()}, new RowMapper<YwReportData>() {
            @Override
            public YwReportData mapRow(ResultSet rs, int rowNum) throws SQLException {
                YwReportData result = new YwReportData();
                result.setId(rs.getString(1));
                result.setProjectId(rs.getString(2));
                result.setCustomerId(rs.getString(3));
                result.setTemplateCode(rs.getString(4));
                result.setReportAddr(rs.getString(5));
                return result;
            }
        });
        return CollectionUtils.isEmpty(reports) ? null : reports.get(0);
    }

    /**
     * 前期设计需求不明确，后面又增加各种情况，导致原始设计不兼容，现在需要进行各种条件判断
     * @param ywReportData
     * @return
     */
    public YwReportData queryReportTemplate(YwReportData ywReportData) {
        String exportRecordId = ywReportData.getId();
        String templateTypeCode = ywReportData.getTemplateTypeCode();
        /**
         * 1、根据模板编号，拉取报告模板目录;
         * 模板类型编号为HTBF时，通过contractType去查询模板（特殊）
         */
        if(ReportEnum.TEMPLATE_TYPE.HTBF.getValue().equals(ywReportData.getTemplateTypeCode())){
            ywReportData = queryHTBFReportData(ywReportData);
        }else if(ReportEnum.TEMPLATE_TYPE.DBQR.getValue().equals(ywReportData.getTemplateTypeCode())){
            ywReportData = querySingletonReportTemplate(ywReportData);
        }else if(ReportEnum.TEMPLATE_TYPE.FKTZ.getValue().equals(ywReportData.getTemplateTypeCode())){
            ywReportData = querySingletonReportTemplate(ywReportData);
        }else if(ReportEnum.TEMPLATE_TYPE.BHBG.getValue().equals(ywReportData.getTemplateTypeCode())){
            ywReportData = queryBHBGReportTemplate(ywReportData);
        }else if(ReportEnum.TEMPLATE_TYPE.YJBG.getValue().equals(ywReportData.getTemplateTypeCode())){
            ywReportData = queryYJBGReportTemplate(ywReportData);
        }else if(ReportEnum.TEMPLATE_TYPE.DCBG.getValue().equals(ywReportData.getTemplateTypeCode())){
            ywReportData = queryDCBGReportTemplate(ywReportData);
        }else{
            ywReportData = queryReportData(ywReportData);
        }
        if(ywReportData != null){
            ywReportData.setExportRecordId(exportRecordId);
            ywReportData.setTemplateTypeCode(templateTypeCode);
        }
        return ywReportData;
    }

    /**
     * 代偿报告
     * @param entity
     * @return
     */
    private YwReportData queryDCBGReportTemplate(YwReportData entity) {
        String strSql = "SELECT t.ID,t.PROJECT_ID,t.CUST_ID,t.TEMPLATE_CODE,t.REPORT_ADDR,t.compensation_report_type" +
                " FROM yw_report_data t WHERE t.PROJECT_ID=? AND t.compensation_report_type=? " +
                " AND  t.TEMPLATE_TYPE_CODE=?  ";
        List<YwReportData> reports = jdbcTemplate.query(strSql, new Object[]{entity.getProjectId(),
                entity.getCompensationReportType(),entity.getTemplateTypeCode()}, new RowMapper<YwReportData>() {
            @Override
            public YwReportData mapRow(ResultSet rs, int rowNum) throws SQLException {
                YwReportData result = new YwReportData();
                result.setId(rs.getString(1));
                result.setProjectId(rs.getString(2));
                result.setCustomerId(rs.getString(3));
                result.setTemplateCode(rs.getString(4));
                result.setReportAddr(rs.getString(5));
                result.setCompensationReportType(rs.getString(6));
                return result;
            }
        });
        return CollectionUtils.isEmpty(reports) ? null : reports.get(0);
    }

    /**
     * 预警报告
     * @param entity
     * @return
     */
    private YwReportData queryYJBGReportTemplate(YwReportData entity) {
        String strSql = "SELECT t.ID,t.PROJECT_ID,t.CUST_ID,t.TEMPLATE_CODE,t.REPORT_ADDR,t.warn_report_type" +
                " FROM yw_report_data t WHERE t.PROJECT_ID=? AND t.warn_report_type=? " +
                " AND  t.TEMPLATE_TYPE_CODE=?  ";
        List<YwReportData> reports = jdbcTemplate.query(strSql, new Object[]{entity.getProjectId(),
                entity.getWarnReportType(),entity.getTemplateTypeCode()}, new RowMapper<YwReportData>() {
            @Override
            public YwReportData mapRow(ResultSet rs, int rowNum) throws SQLException {
                YwReportData result = new YwReportData();
                result.setId(rs.getString(1));
                result.setProjectId(rs.getString(2));
                result.setCustomerId(rs.getString(3));
                result.setTemplateCode(rs.getString(4));
                result.setReportAddr(rs.getString(5));
                result.setWarnReportType(rs.getString(6));
                return result;
            }
        });
        return CollectionUtils.isEmpty(reports) ? null : reports.get(0);
    }

    /**
     * 保后报告
     * @param entity
     * @return
     */
    private YwReportData queryBHBGReportTemplate(YwReportData entity) {
        String strSql = "SELECT t.ID,t.PROJECT_ID,t.CUST_ID,t.TEMPLATE_CODE,t.REPORT_ADDR,t.after_guarantee_report_type" +
                " FROM yw_report_data t WHERE t.PROJECT_ID=? AND t.after_guarantee_report_type=? " +
                " AND  t.TEMPLATE_TYPE_CODE=?  ";
        List<YwReportData> reports = jdbcTemplate.query(strSql, new Object[]{entity.getProjectId(),
                entity.getAfterGuaranteeReportType(),entity.getTemplateTypeCode()}, new RowMapper<YwReportData>() {
            @Override
            public YwReportData mapRow(ResultSet rs, int rowNum) throws SQLException {
                YwReportData result = new YwReportData();
                result.setId(rs.getString(1));
                result.setProjectId(rs.getString(2));
                result.setCustomerId(rs.getString(3));
                result.setTemplateCode(rs.getString(4));
                result.setReportAddr(rs.getString(5));
                result.setAfterGuaranteeReportType(rs.getString(6));
                return result;
            }
        });
        return CollectionUtils.isEmpty(reports) ? null : reports.get(0);
    }
}
